const express = require('express');
const productRouter = express.Router();
const { sendResult } = require('../utils/index')

const mysql = require('mysql')
const db = mysql.createPool({
    host: "127.0.0.1",
    user: "root",
    password: "admin123",
    database: "mydatabase_01",
    port: 3306,
})


const table = {
    name: 'product',
}

productRouter.use(express.json());
productRouter.use(express.urlencoded({ extended: true }));

//不同渠道今日的销售额
productRouter.get('/product/list', (req, res) => {
    const { pageSize, pages } = req.query
    const startIndex = (pages - 1) * pageSize
    console.log(pageSize, pages, startIndex, 'list');
    let sql = `SELECT * FROM ${table.name} limit ${pageSize} offset ${startIndex}`

    if (pages && pageSize) {

        db.query(sql, (err, result) => {
            if (err) {
                console.log(err); return

            } else {
                sendResult(res, result)
                return
            }
        })
    } else {
        sql = `SELECT * FROM ${table.name}`
        db.query(sql, (err, result) => {
            if (err) {
                console.log(err); return
            }
            else {
                sendResult(res, result)
                return
            }
        })
    }
})

//产量前十
function filterSales(arr, key) {
    return arr.filter(item => item.salesChannel === key).reduce((pre, cur) => pre + cur.sales, 0)
}
productRouter.get('/product/salesChannelTop10', (req, res) => {
    let sql = `SELECT salesChannel,sales FROM ${table.name};`
    let sql2 = `SELECT SUM(sales) AS totalSales FROM ${table.name};`



    db.query(sql, (err, result) => {
        if (err) {
            console.log(err); return
        }
        else {
            const data = {
                list: [
                    {
                        name: '线下',
                        key: 0,
                        value: filterSales(result, 0),
                        percent: 0
                    },
                    {
                        name: '线上',
                        key: 1,
                        value: filterSales(result, 1),
                        percent: 0
                    },
                    {
                        name: '其他',
                        key: 2,
                        value: filterSales(result, 2),
                        percent: 0
                    }
                ],
                total: 0
            }
            db.query(sql2, (err, result) => {
                if (err) {
                    console.log(err); return
                }
                data.total = result[0].totalSales
                sendResult(res, data)
            })
        }
    })
})

// 销量榜前五
productRouter.get('/product/saleTop5', (req, res) => {
    let sql = `SELECT * FROM ${table.name} ORDER BY sales DESC LIMIT 5`
    db.query(sql, (err, result) => {
        if (err) {
            console.log(err); return
        }
        else {
            let data = {
                xData: '',
                yData: ''
            }
            data.xData = result.map(item => item.name)
            data.yData = result.map(item => item.products)
            sendResult(res, result)
        }
    })
})


//产量前十
productRouter.get('/product/productionTop10', (req, res) => {
    let sql = `SELECT * FROM ${table.name} ORDER BY products DESC LIMIT 10`
    db.query(sql, (err, result) => {
        if (err) {
            console.log(err); return
        }
        else {
            let data = {
                xData: '',
                yData: ''
            }
            data.xData = result.map(item => item.name)
            data.yData = result.map(item => item.products)
            sendResult(res, data)
        }
    })
})


//新增 修改
productRouter.post('/product/edit', (req, res) => {
    let { id } = req.query
    let { name, sales, salesChannel, stock, todayPrice, yestodayPrice, futurePrice, products, outProducts, productType, repurchaseCounts } = req.body
    console.log(req.body);

    if (id) {
        if (name && sales && salesChannel && stock && todayPrice && yestodayPrice && futurePrice && products && outProducts && productType && repurchaseCounts) {
            db.query(`UPDATE ${table.name} SET name = '${name}',sales = '${sales}',salesChannel = '${salesChannel}',products= '${products}', outProducts ='${outProducts}', stock = '${stock}', todayPrice='${todayPrice}',yestodayPrice ='${yestodayPrice}',futurePrice='${futurePrice}',productType='${productType}' ,repurchaseCounts=${repurchaseCounts} WHERE id = ${id}`, (err, result) => {
                if (err) {
                    res.status(500).send(err)
                } else {
                    sendResult(res, {
                        msg: '修改成功'
                    })
                }
            }
            )
        } else {
            sendResult(res, {
                msg: '请输入完整信息'
            }, 201)
        }
    } else {
        if (!name || !sales || !salesChannel || !stock || !todayPrice || !yestodayPrice || !futurePrice || !products || !outProducts || !productType || !repurchaseCounts) {

            sendResult(res, {
                msg: '请输入完整信息'
            }, 201)

        } else {
            db.query(`INSERT INTO ${table.name} (name,  sales, salesChannel, stock, todayPrice,yestodayPrice,futurePrice,products,outProducts,productType,repurchaseCounts) VALUES ('${name}', '${sales}', '${salesChannel}', '${stock}' ,'${todayPrice}', '${yestodayPrice}', '${futurePrice}', '${products}', '${outProducts}','${productType}' ,'${repurchaseCounts}')`, (err, result) => {
                if (err) {
                    console.log(err);
                    if (err.errno === 1062) {
                        sendResult(res, {
                            msg: '该数据已存在'
                        }, 201)
                    } else {
                        res.status(500).send(err)
                    }
                } else {
                    sendResult(res, {
                        msg: '新增成功！'
                    })
                }
            })
        }

    }

})

//删除
productRouter.get('/product/del', (req, res) => {
    let { id } = req.query
    db.query(`DELETE FROM ${table.name} WHERE id = ${id} `, (err, result) => {
        if (err) {
            res.status(500).send(err)
        } else {
            sendResult(res, {
                msg: '删除成功'
            })
        }
    })
})



module.exports = productRouter